package com.hundun;

import com.fr.data.AbstractTableData;
import com.fr.data.TableDataException;
import com.fr.report.parameter.Parameter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URISyntaxException;
import java.util.*;


/**
 * User: xf
 * Date: 12-11-13
 * Time: 下午4:15
 */
public class GMTable extends AbstractTableData {
    private String[] columnNames;
    private List<Map> datas = new ArrayList();
    private List<Map> periodlist = new ArrayList();
    private List<Map> cidlist = new ArrayList();
    private boolean persist=false;
    String pdid = "";
    private JdbcTemplate jdbcTemplate = new JdbcTemplate();

    public GMTable() {

        columnNames = new String[]{"date", "fe","gm"};
        this.parameters = new Parameter[]{new Parameter("pdid")};
        // for test
        //this.parameters = new Parameter[]{new Parameter("pdid","45")};
        // 定义程序数据集列名


    }
    public GMTable(String pdid,boolean persist) {

            columnNames = new String[]{"date", "fe","gm"};
            this.parameters = new Parameter[]{new Parameter("pdid",pdid)};
            this.persist=true;
            // for test
            //this.parameters = new Parameter[]{new Parameter("pdid","45")};
            // 定义程序数据集列名


        }

    @Override
    public int getColumnCount() throws TableDataException {
        return columnNames.length;  //To change body of implemented methods use File | Settings | File Templates.
    }

    @Override
    public String getColumnName(int i) throws TableDataException {
        return columnNames[i];
    }

    @Override
    public int getRowCount() throws TableDataException {
        init();
        return datas.size();
    }

    @Override
    public Object getValueAt(int i, int i1) {
        Map obj = datas.get(i);
        if (i1 == 0) return obj.get("date");
        if (i1 == 1) return obj.get("fe");
        if(i1==2) return obj.get("gm");
        return null;
    }

    public void init() {
        // 确保只被执行一次

        if (datas.size() > 0) return;

        for (int i = 0; i < parameters.length; i++) {
            if (parameters[i].getName().equals("pdid")) // 做匹配
                pdid = parameters[i].getValue().toString();
        }
        if (pdid.equals("")) return;
        try {
            String rootpath = GMTable.class.getResource("/").toURI().getPath();
            //System.out.printf("rootpath:"+rootpath);
            Properties props = new Properties();
            InputStream in = new FileInputStream(rootpath + "../WEB-INF/prop/weaver.properties");
            props.load(in);
            String driver = props.getProperty("DriverClasses");
            String url = props.getProperty("ecology.url");
            String username = props.getProperty("ecology.user");
            String password = props.getProperty("ecology.password");
            DriverManagerDataSource ds = new DriverManagerDataSource();
            ds.setDriverClassName(driver);
            ds.setUrl(url);
            ds.setUsername(username);
            ds.setPassword(password);
            jdbcTemplate.setDataSource(ds);
        } catch (Exception e) {
            e.printStackTrace();
        }

        getPeroidList();
        getCidList();
        getDatas();
        // System.out.print(datas.size());
    }

    private List getPeroidList() {//获取期数列表

        String sql = "select a.productname as productid,CONVERT(varchar(12) , dateadd(dd,-day(dateadd(m,1,riqi)),dateadd(m,1,riqi)), 23 ) as date,max(jingzhi) as jz " +
                "from formtable_main_119 a,formtable_main_119_dt1 b, " +
                "(select a.productname,substring(b.riqi,0,8)as month,MAX(b.riqi) as date " +
                "from formtable_main_119 a,formtable_main_119_dt1 b " +
                "where a.id=b.mainid " +
                "and a.productname=? " +
                "group by a.productname,substring(riqi,0,8))c " +
                "where a.id=b.mainid and a.productname=c.productname and b.riqi=c.date " +
                "and a.productname=? " +
                "group by a.productname,riqi";
        periodlist = jdbcTemplate.queryForList(sql,new Object[]{pdid,pdid});
        return periodlist;
    }

    private List getCidList() {//获取客户列表
        String sql = "select distinct c.cid " +
                "from ( " +
                "select b.sgr as cid,QW18 as productid,Q7 as date,b.sgfe/10000 as fe,1 as type,0 as jz  " +
                "from formtable_main_57 a,formtable_main_57_dt1 b " +
                "where a.id=b.mainid " +
                "union " +
                "select b.kehu as cid,a.d7 as productid,a.q5 as date,b.fenshu/10000 as fe,2 as type,0 as jz " +
                "from formtable_main_59 a,formtable_main_59_dt1 b " +
                "where a.id=b.mainid " +
                "union " +
                "select b.A5 as cid,b.A1 as productid,a.q7 as date,b.A2/10000 as fe,3 as type,0 as jz " +
                "from formtable_main_58 a,formtable_main_58_dt1 b " +
                "where a.id=b.mainid " +
                ")c where   c.productid =?";
        cidlist = jdbcTemplate.queryForList(sql,new Object[]{pdid});
        return cidlist;
    }

    private List getDatas() {
        for (Map periodmap : periodlist) {
            String period = (String) periodmap.get("date");
            float jz=string2Float(periodmap.get("jz").toString(), 0);
            float[] fe_gm = getData(period,jz);
            Map obj = new HashMap();
            obj.put("date", period);
            obj.put("fe", fe_gm[0]);
            obj.put("gm",fe_gm[1]);
            datas.add(obj);
            if (persist) {
                try{
                jdbcTemplate.update("insert into hundun_guimo(pdid,date,fe,guimo) values(?,?,?,?)", new Object[]{pdid, period, fe_gm[0], fe_gm[1]});
                }catch(Exception e){e.printStackTrace();}
            }
        }
        return datas;
    }

    //当期总份额
    private float[] getData(String period,float jz) {
        //System.out.println("----------"+period+"-----------");
        float fe = 0;
        float gm=0;
        for (Map cidmap : cidlist) {
            int cid = string2Int(cidmap.get("cid").toString(), -1);
            //System.out.println("cid:"+cid);
            float pv= getPeriodValue(period, cid);
            //System.out.println("value:"+pv);
            fe += pv;
        }
        gm=fe*jz;
        //System.out.println("total:"+fe);
        return new float[]{fe,gm};

    }

    //某客户当期份额
    private float getPeriodValue(String period, int cid) {
        //本期初始份额
        String sql = "select  top 1 c.date,c.fe " +
                "from ( " +
                "select b.kehu as cid,a.d7 as productid,a.q5 as date,b.fenshu/10000 as fe,2 as type,0 as jz " +
                "from formtable_main_59 a,formtable_main_59_dt1 b " +
                "where a.id=b.mainid " +
                ")c where  c.cid=? and c.productid =? and c.date<=? and c.type=2 order by c.date desc";
        List<Map> initdata = jdbcTemplate.queryForList(sql, new Object[]{cid,pdid,period});
        String initdate = "1970-01-01";
        float initvalue = 0;
        if (initdata.size() > 0) {
            initdate = (String) initdata.get(0).get("date");
            initvalue = string2Float(initdata.get(0).get("fe").toString(), 0);
        }
        //本期买入份额
        sql = "select  isnull(sum(c.fe),0) as fe " +
                "from ( " +
                "select b.sgr as cid,QW18 as productid,Q7 as date,sum(b.sgfe/10000) as fe,max(1) as type,max(0) as jz " +
                "from formtable_main_57 a,formtable_main_57_dt1 b " +
                "where a.id=b.mainid " +
				"group by b.sgr,QW18,Q7 " +
                ")c where  c.cid=? and c.productid =? and c.type=1 and c.date>? and c.date<=?";
        float purvalue = (Float) jdbcTemplate.queryForObject(sql, new Object[]{cid,pdid, initdate, period}, Float.class);
        //本期赎回份额
        sql = "select  isnull(sum(c.fe),0) as fe " +
                "from ( " +
                "select b.A5 as cid,b.A1 as productid,a.q7 as date,sum(b.A2/10000) as fe,max(3) as type,max(0) as jz " +
                "from formtable_main_58 a,formtable_main_58_dt1 b " +
                "where a.id=b.mainid and b.A2>0 " +
				"group by b.A5,b.A1,a.q7" +
                ")c where  c.cid=? and c.productid =? and c.type=3 and c.date>? and c.date<=?";
        float retvalue = (Float) jdbcTemplate.queryForObject(sql, new Object[]{cid, pdid,initdate, period}, Float.class);
        return initvalue + purvalue - retvalue;
    }

    public float string2Float(String strValue, float defValue) {
        try {
            return Float.parseFloat(strValue);
        } catch (Exception ex) {
            return defValue;
        }
    }

    public int string2Int(String strValue, int defValue) {
        try {
            return Integer.parseInt(strValue);
        } catch (Exception ex) {
            return defValue;
        }
    }

    public static void main(String[] args) throws URISyntaxException {
        //at first,uncomment for test
        String rootpath = GMTable.class.getResource("/").toURI().getPath();
        System.out.printf(rootpath);
        //GMTable gm=new GMTable();
        //gm.init();
    }

}
